package com.coolwen.controller;

import com.coolwen.entity.*;
import com.coolwen.service.councilor.CouncilorService;
import com.coolwen.service.expert.ExpertService;
import com.coolwen.service.expertAndCouncilor.ExpertAndCouncilorService;
import com.coolwen.service.major.MajorService;
import com.coolwen.service.student.StudentService;
import com.coolwen.service.total.TotalService;
import com.coolwen.service.upload.UploadService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by nieyu on 2016/9/1.
 */
@Controller
@RequestMapping(value = "/upload")
public class UploadController extends HttpServlet {

    @Autowired
    UploadService uploadService;

    @Autowired
    StudentService studentService;

    @Autowired
    ExpertService expertService;

    @Autowired
    CouncilorService councilorService;

    @Autowired
    ExpertAndCouncilorService expertAndCouncilorService;

    @Autowired
    TotalService totalService;

    @Autowired
    MajorService majorService;

    @RequestMapping(value = "/student/uploadStudent", method = RequestMethod.GET)
    public String uploadStudent(Model model, HttpServletRequest request, String md5) {

        model.addAttribute("md5", md5);


        return "page/upload_student";
    }

    @RequestMapping(value = "/student/upload", method = RequestMethod.POST)
    public String uploadAllStudent(HttpServletRequest request, Model model) throws IOException {
        Upload upload = new Upload();

        String md = request.getParameter("md5");

        List<Upload> uploads = uploadService.selectByMd5(md);


        if (uploads.isEmpty()) {

            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            String logoPathDir = "/resources/upload";

            String logoRealPathDir = request.getSession().getServletContext().getRealPath(logoPathDir);

            File logoSaveFile = new File(logoRealPathDir);
            if (!logoSaveFile.exists())
                logoSaveFile.mkdirs();
            /** 页面控件的文件流* */
            MultipartFile multipartFile = multipartRequest.getFile("files");
            /** 获取文件的后缀* */
            String suffix = multipartFile.getOriginalFilename().substring(
                    multipartFile.getOriginalFilename().lastIndexOf("."));


            /** 使用UUID生成文件名称* */
            String logImageName = UUID.randomUUID().toString() + suffix;// 构建文件名称
            /** 拼成完整的文件保存路径加文件* */
            String fileName = logoRealPathDir + File.separator + logImageName;
            File file = new File(fileName);
            try {
                multipartFile.transferTo(file);
            } catch (IllegalStateException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            /** 打印出上传到服务器的文件的绝对路径* */
//        System.out.println("****************"+fileName+"**************");
            HSSFWorkbook wb = null;
            XSSFWorkbook wb2 = null;

            if (suffix.equals(".xls")) {
                InputStream stream = new FileInputStream(fileName);
                wb = new HSSFWorkbook(stream);
                Sheet sheet1 = wb.getSheetAt(0);

                for (int i = 2; i < 3; i++) {

                    Row row = sheet1.getRow(i);

                    Cell cell = row.getCell(0);
                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);

                    if (!cell1.getStringCellValue().contains("年级")) {

                        model.addAttribute("error", "上传文件第二列非年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("专业")) {

                        model.addAttribute("error", "上传文件第三列不是专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("课程")) {

                        model.addAttribute("error", "上传文件第四列不是课程名称列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第五列不是参评人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第六列不是应评人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("平均分")) {

                        model.addAttribute("error", "上传文件第七列不是平均分列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                String semster = "";

                for (int i = 1; i < 2; i++) {
                    Row row = sheet1.getRow(i);

                    Cell cell1 = row.getCell(1);

                    semster = cell1.getStringCellValue();

                }

                for (int i = 3; i <= sheet1.getLastRowNum(); i++) {
                    Row row = sheet1.getRow(i);
                    Student student = new Student();
                    Total total = new Total();

                    String grade = "";

                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);

                    if ((row.getCell(3) == null|| cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) && (row.getCell(4) == null|| cell4.getCellType() == HSSFCell.CELL_TYPE_BLANK) && (row.getCell(5) == null|| cell5.getCellType() == HSSFCell.CELL_TYPE_BLANK)) {
                        continue;
                    }

                    if (row.getCell(1) == null && cell1.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        student.setGrade("");
                    }
                    if (row.getCell(1) != null && cell1.getCellType() != HSSFCell.CELL_TYPE_BLANK) {

                        switch (cell4.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                grade = cell1.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
                                String sGrade = cell1.getStringCellValue();
                                System.out.println("++++=" + sGrade);
                                grade = sGrade.substring(0, 4);
                                break;
                        }

                        student.setGrade(grade);
                        total.setGrade(grade);
                    }
                    if (row.getCell(2) == null) {
                        student.setMajor("");
                    } else {
                        student.setMajor(cell2.getStringCellValue());
                        total.setMajor(cell2.getStringCellValue());
                    }
                    if (row.getCell(3) == null && cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        student.setCourse("");
                        student.setCourseNumber("");
                    } else if (row.getCell(3) != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                        String cellMess = cell3.getStringCellValue();
                        student.setCourseNumber(cellMess.substring(cellMess.indexOf("[")+1,cellMess.indexOf("]")));
                        student.setCourse(cellMess.substring(cellMess.lastIndexOf("]")+1));
                        total.setCourse(cellMess.substring(cellMess.lastIndexOf("]") + 1));
                    }
                    if (row.getCell(4) == null) {
                        student.setNumbers(null);
                    } else {
                        String sNum = "";
                        int sNumber = 0;

                        switch (cell4.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sNum = cell4.getStringCellValue();

                                if (!sNum.equals("")) {
                                    if (sNum.length() == 2 && isNum(sNum.substring(0, 1))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 1));
                                    }
                                    if (sNum.length() == 3 && isNum(sNum.substring(0, 2))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 2));
                                    }
                                    if (sNum.length() == 4 && isNum(sNum.substring(0, 3))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 3));
                                    }
                                } else {
                                    sNumber = 0;
                                }
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                String num = String.valueOf(cell4.getNumericCellValue());

                                if (num.length() == 3 && isNum(num.substring(0, 1))) {
                                    sNumber = Integer.valueOf(num.substring(0, 1));
                                }
                                if (num.length() == 4 && isNum(num.substring(0, 2))) {
                                    sNumber = Integer.valueOf(num.substring(0, 2));
                                }
                                if (num.length() == 5 && isNum(num.substring(0, 3))) {
                                    sNumber = Integer.valueOf(num.substring(0, 3));
                                }
                                break;
                        }
                        student.setNumbers(sNumber);
                    }
                    if (row.getCell(5) == null) {
                        student.setTotalNumber(null);
                    } else {
                        String sNum = "";
                        int sNumber = 0;

                        switch (cell5.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sNum = cell5.getStringCellValue();

                                if (!sNum.equals("")) {
                                    if (sNum.length() == 2 && isNum(sNum.substring(0, 1))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 1));
                                    }
                                    if (sNum.length() == 3 && isNum(sNum.substring(0, 2))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 2));
                                    }
                                    if (sNum.length() == 4 && isNum(sNum.substring(0, 3))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 3));
                                    }
                                } else {
                                    sNumber = 0;
                                }
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                String num = String.valueOf(cell5.getNumericCellValue());

                                if (num.length() == 3 && isNum(num.substring(0, 1))) {
                                    sNumber = Integer.valueOf(num.substring(0, 1));
                                }
                                if (num.length() == 4 && isNum(num.substring(0, 2))) {
                                    sNumber = Integer.valueOf(num.substring(0, 2));
                                }
                                if (num.length() == 5 && isNum(num.substring(0, 3))) {
                                    sNumber = Integer.valueOf(num.substring(0, 3));
                                }
                                break;
                        }
                        student.setTotalNumber(sNumber);
                    }
                    if (row.getCell(6) == null) {
                        student.setScore(null);
                    } else {
                        Double score = cell6.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        student.setScore(scoreLast);
                        total.setStudentScore(scoreLast);
                    }


                    String major = student.getMajor();

                    List<Majors> majors = majorService.selectInfo(major);
                    for (int j = 0; j < majors.size(); j++) {
                        Majors major1 = majors.get(j);
                        if (major.equals(major1.getMajor()) && grade.equals(major1.getGrade())) {
                            student.setDepartment(major1.getDepartment());
                            total.setDepartment(major1.getDepartment());

                        }
                    }
                    if (semster.equals("")) {
                        student.setSemster("");
                    } else {
                        student.setSemster(semster);
                    }

                    studentService.insertSelective(student);
                    totalService.insertSelective(total);

                }
            } else if (suffix.equals(".xlsx")) {
                InputStream stream = new FileInputStream(fileName);
                wb2 = new XSSFWorkbook(stream);
                Sheet sheet1 = wb2.getSheetAt(0);

                for (int i = 2; i < 3; i++) {

                    Row row = sheet1.getRow(i);

                    Cell cell = row.getCell(0);
                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);

                    if (!cell1.getStringCellValue().contains("年级")) {

                        model.addAttribute("error", "上传文件第二列非年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("专业")) {

                        model.addAttribute("error", "上传文件第三列不是专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("课程")) {

                        model.addAttribute("error", "上传文件第四列不是课程名称列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第五列不是参评人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第六列不是应评人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("平均分")) {

                        model.addAttribute("error", "上传文件第七列不是平均分列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                String semster = "";

                for (int i = 1; i < 2; i++) {
                    Row row = sheet1.getRow(i);

                    Cell cell1 = row.getCell(1);

                    semster = cell1.getStringCellValue();

                }

                for (int i = 3; i <= sheet1.getLastRowNum(); i++) {
                    Row row = sheet1.getRow(i);
                    Student student = new Student();
                    Total total = new Total();

                    String grade = "";

                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);

                    if (row.getCell(1) == null && row.getCell(2) == null && row.getCell(3) == null && row.getCell(4) == null && row.getCell(5) == null
                            ) {
                        continue;
                    }

                    if (row.getCell(1) == null && cell1.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        student.setGrade("");
                    }
                    if (row.getCell(1) != null && cell1.getCellType() != XSSFCell.CELL_TYPE_BLANK) {

                        switch (cell4.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                grade = cell1.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                row.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);
                                String sGrade = cell1.getStringCellValue();
                                grade = sGrade.substring(0, 4);
                                break;
                        }

                        student.setGrade(grade);
                        total.setGrade(grade);
                    }
                    if (row.getCell(2) == null) {
                        student.setMajor("");
                    } else {
                        student.setMajor(cell2.getStringCellValue());
                        total.setMajor(cell2.getStringCellValue());
                    }
                    if (row.getCell(3) == null && cell3.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        student.setCourse("");
                        student.setCourseNumber("");
                    } else if (row.getCell(3) != null && cell3.getCellType() != XSSFCell.CELL_TYPE_BLANK){
                        String cellMess = cell3.getStringCellValue();
                        student.setCourseNumber(cellMess.substring(cellMess.indexOf("[")+1,cellMess.indexOf("]")));
                        student.setCourse(cellMess.substring(cellMess.lastIndexOf("]") + 1));
                        total.setCourse(cellMess.substring(cellMess.lastIndexOf("]") + 1));
                    }
                    if (row.getCell(4) == null) {
                        student.setNumbers(null);
                    } else {
                        String sNum = "";
                        int sNumber = 0;

                        switch (cell4.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sNum = cell4.getStringCellValue();

                                if (!sNum.equals("")) {
                                    if (sNum.length() == 2 && isNum(sNum.substring(0, 1))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 1));
                                    }
                                    if (sNum.length() == 3 && isNum(sNum.substring(0, 2))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 2));
                                    }
                                    if (sNum.length() == 4 && isNum(sNum.substring(0, 3))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 3));
                                    }
                                } else {
                                    sNumber = 0;
                                }
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                String num = String.valueOf(cell4.getNumericCellValue());

                                if (num.length() == 3 && isNum(num.substring(0, 1))) {
                                    sNumber = Integer.valueOf(num.substring(0, 1));
                                }
                                if (num.length() == 4 && isNum(num.substring(0, 2))) {
                                    sNumber = Integer.valueOf(num.substring(0, 2));
                                }
                                if (num.length() == 5 && isNum(num.substring(0, 3))) {
                                    sNumber = Integer.valueOf(num.substring(0, 3));
                                }
                                break;
                        }
                        student.setNumbers(sNumber);
                    }
                    if (row.getCell(5) == null) {
                        student.setTotalNumber(null);
                    } else {
                        String sNum = "";
                        int sNumber = 0;

                        switch (cell5.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sNum = cell5.getStringCellValue();

                                if (!sNum.equals("")) {
                                    if (sNum.length() == 2 && isNum(sNum.substring(0, 1))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 1));
                                    }
                                    if (sNum.length() == 3 && isNum(sNum.substring(0, 2))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 2));
                                    }
                                    if (sNum.length() == 4 && isNum(sNum.substring(0, 3))) {
                                        sNumber = Integer.valueOf(sNum.substring(0, 3));
                                    }
                                } else {
                                    sNumber = 0;
                                }
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                String num = String.valueOf(cell5.getNumericCellValue());

                                if (num.length() == 3 && isNum(num.substring(0, 1))) {
                                    sNumber = Integer.valueOf(num.substring(0, 1));
                                }
                                if (num.length() == 4 && isNum(num.substring(0, 2))) {
                                    sNumber = Integer.valueOf(num.substring(0, 2));
                                }
                                if (num.length() == 5 && isNum(num.substring(0, 3))) {
                                    sNumber = Integer.valueOf(num.substring(0, 3));
                                }
                                break;
                        }
                        student.setTotalNumber(sNumber);
                    }
                    if (row.getCell(6) == null) {
                        student.setScore(null);
                    } else {
                        Double score = cell6.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        student.setScore(scoreLast);
                        total.setStudentScore(scoreLast);
                    }


                    String major = student.getMajor();

                    List<Majors> majors = majorService.selectInfo(major);
                    for (int j = 0; j < majors.size(); j++) {
                        Majors major1 = majors.get(j);
                        if (major.equals(major1.getMajor()) && grade.equals(major1.getGrade())) {
                            student.setDepartment(major1.getDepartment());
                            total.setDepartment(major1.getDepartment());
                        }
                    }
                    if (semster.equals("")) {
                        student.setSemster("");
                    } else {
                        student.setSemster(semster);
                    }

                    studentService.insertSelective(student);
                    totalService.insertSelective(total);

                }
            } else {
                model.addAttribute("error", "上传失败，请上传Excel类型的文件！");
                return "errorPage/format_error";
            }

            upload.setMd5(md);

            uploadService.insertSelective(upload);

            return "upload/upload_successful";
        } else {
            return "errorPage/upload_error";
        }

    }

    @RequestMapping(value = "/expert/uploadExpert", method = RequestMethod.GET)
    public String uploadExpert(Model model, String md5) {

        model.addAttribute("md5", md5);


        return "page/upload_expert";
    }

    @RequestMapping(value = "/expert/uploadExpert", method = RequestMethod.POST)
    public String uploadExperts(Model model, String md5, HttpServletRequest request) throws IOException {


        Upload upload = new Upload();

        String md = request.getParameter("md5");


        List<Upload> uploads = uploadService.selectByMd5(md);


        if (uploads.isEmpty()) {

            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            String logoPathDir = "/resources/upload";

            String logoRealPathDir = request.getSession().getServletContext().getRealPath(logoPathDir);

            File logoSaveFile = new File(logoRealPathDir);
            if (!logoSaveFile.exists())
                logoSaveFile.mkdirs();
            /** 页面控件的文件流* */
            MultipartFile multipartFile = multipartRequest.getFile("files");
            /** 获取文件的后缀* */
            String suffix = multipartFile.getOriginalFilename().substring(
                    multipartFile.getOriginalFilename().lastIndexOf("."));

            /** 使用UUID生成文件名称* */
            String logImageName = UUID.randomUUID().toString() + suffix;// 构建文件名称
            /** 拼成完整的文件保存路径加文件* */
            String fileName = logoRealPathDir + File.separator + logImageName;
            File file = new File(fileName);
            try {
                multipartFile.transferTo(file);
            } catch (IllegalStateException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            /** 打印出上传到服务器的文件的绝对路径* */
//        System.out.println("****************"+fileName+"**************");

            HSSFWorkbook wb = null;
            XSSFWorkbook wb2 = null;


            if (suffix.equals(".xls")) {
                InputStream stream = new FileInputStream(fileName);
                wb = new HSSFWorkbook(stream);
                Sheet sheet1 = wb.getSheetAt(0);

                for (int i = 1; i < 2; i++) {
                    Row row = sheet1.getRow(i);

                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);

                    if (!cell1.getStringCellValue().contains("序号")) {
                        model.addAttribute("error", "上传文件第一列非序号列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("部") && cell2.getStringCellValue().contains("系")) {

                        model.addAttribute("error", "上传文件第二列非系部列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("专业")) {

                        model.addAttribute("error", "上传文件第三列非专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("年级")) {

                        model.addAttribute("error", "上传文件第四列非年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("教师") && !cell5.getStringCellValue().contains("老师")) {

                        model.addAttribute("error", "上传文件第五列非授课教师[单位]列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("课程")) {

                        model.addAttribute("error", "上传文件第六列非课程名称列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell7.getStringCellValue().contains("日期")) {

                        model.addAttribute("error", "上传文件第七列非日期（节次）列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell8.getStringCellValue().contains("教室")) {

                        model.addAttribute("error", "上传文件第八列非教室列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell9.getStringCellValue().contains("班级")) {

                        model.addAttribute("error", "上传文件第九列非班级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell10.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第十列非实到人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell11.getStringCellValue().contains("分")) {

                        model.addAttribute("error", "上传文件第十一列非综合评分列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell12.getStringCellValue().contains("备注")) {

                        model.addAttribute("error", "上传文件第十二列非备注列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                for (int i = 2; i <= sheet1.getLastRowNum(); i++) {
                    Expert expert = new Expert();
                    ExpertAndCouncilor expertAndCouncilor = new ExpertAndCouncilor();
                    Total total = new Total();

                    Row row = sheet1.getRow(i);

                    if (null == row) {
                        break;
                    }
                    String grade = "";

                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);
                    Cell cell7 = row.getCell(8);
                    Cell cell8 = row.getCell(9);
                    Cell cell9 = row.getCell(10);
                    Cell cell10 = row.getCell(11);


                    if (row.getCell(0) == null && row.getCell(1) == null && row.getCell(2) == null && row.getCell(3) == null && row.getCell(4) == null && row.getCell(5) == null
                            ) {
                        continue;
                    }

                    if (row.getCell(1) == null) {
                        expert.setDepartment("");
                        expertAndCouncilor.setDepartment("");
                        total.setDepartment("");
                    } else {
                        if (cell1.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            expert.setDepartment(cell1.getStringCellValue());
                            expertAndCouncilor.setDepartment(cell1.getStringCellValue());
                            total.setDepartment(cell1.getStringCellValue());
                        }else {
                            expert.setDepartment("");
                            expertAndCouncilor.setDepartment("");
                            total.setDepartment("");
                        }
                    }
                    if (row.getCell(2) == null) {
                        expert.setMajor("");
                        expertAndCouncilor.setMajor("");
                        total.setMajor("");
                    } else {
                        if (cell2.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            expert.setMajor(cell2.getStringCellValue());
                            expertAndCouncilor.setMajor(cell2.getStringCellValue());
                            total.setMajor(cell2.getStringCellValue());
                        }else {
                            expert.setMajor("");
                            expertAndCouncilor.setMajor("");
                            total.setMajor("");
                        }
                    }
                    if (row.getCell(3) == null && cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        expert.setGrade("");
                        expertAndCouncilor.setGrade("");
                        total.setGrade("");
                    }
                    if (row.getCell(3) != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                        String errorGrade = cell3.getStringCellValue();
                        if (errorGrade.length() < 5) {
                            grade = "";
                            expert.setGrade("");
                            expertAndCouncilor.setGrade("");
                            total.setGrade("");
                        } else {
                            grade = cell3.getStringCellValue().substring(0, 4);
                            if (isNum(grade) == true){
                                expert.setGrade(grade);
                                expertAndCouncilor.setGrade(grade);
                                total.setGrade(grade);
                            }else {
                                expert.setGrade("");
                                expertAndCouncilor.setGrade("");
                                total.setGrade("");
                            }

                        }
                    }
                    if (row.getCell(4) == null) {
                        expert.setTeacher("");
                    } else {
                        if (cell4.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            expert.setTeacher(cell4.getStringCellValue());
                        }else {
                            expert.setTeacher("");
                        }
                    }
                    if (row.getCell(5) == null) {
                        expert.setCourse("");
                        expertAndCouncilor.setCourse("");
                        total.setCourse("");
                    } else {
                        if (cell5.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            expert.setCourse(cell5.getStringCellValue());
                            expertAndCouncilor.setCourse(cell5.getStringCellValue());
                            total.setCourse(cell5.getStringCellValue());
                        }else if (cell5.getCellType() != HSSFCell.CELL_TYPE_STRING){
                            expert.setCourse("");
                            expertAndCouncilor.setCourse("");
                            total.setCourse("");
                        }
                    }
                    if (row.getCell(6) == null) {
                        expert.setSemester("");
                        expertAndCouncilor.setSemester("");
                    } else {
                        String sGrade = null;
                        int sGra = 0;
                        String sMouthOne = null;
                        String sMouthTwo = null;
                        switch (cell6.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sGrade = cell6.getStringCellValue().substring(0, 4);
                                if (isNum(sGrade) == true) {
                                    sGra = Integer.valueOf(sGrade).intValue();
                                }else {
                                    sGra = 0;
                                }
                                sMouthOne = cell6.getStringCellValue().substring(5, 7);
                                sMouthTwo = cell6.getStringCellValue().substring(5, 6);
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                                String data = String.valueOf(cell6.getDateCellValue().toString());
                                String date = dff.format(cell6.getDateCellValue());
                                sGrade = date.substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = date.substring(5, 7);
                                sMouthTwo = date.substring(5, 6);
                                break;
                        }

                        int mouth;
                        if (isNum(sMouthOne) == true) {
                            mouth = Integer.valueOf(sMouthOne).intValue();
                        } else {
                            mouth = Integer.valueOf(sMouthTwo).intValue();
                        }

                        if (sMouthOne != null && sMouthTwo != null) {
                            if (!grade.equals("")) {
                                String sGradeTwo = grade;
                                int iGrade = Integer.valueOf(sGradeTwo).intValue();
                                if ((sGra - iGrade - 1 == -1) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第一学年第二学期");
                                    expertAndCouncilor.setSemester("第一学年第二学期");
                                } else if ((sGra - iGrade == 1) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第二学年第二学期");
                                    expertAndCouncilor.setSemester("第二学年第二学期");
                                } else if ((sGra - iGrade == 2) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第三学年第二学期");
                                    expertAndCouncilor.setSemester("第三学年第二学期");
                                } else if ((sGra - iGrade == 3) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                } else if ((sGra - iGrade == 4) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第四学年第二学期");
                                    expertAndCouncilor.setSemester("第四学年第二学期");
                                } else if ((sGra - iGrade == 0) && mouth == 1) {
                                    expert.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && mouth == 1) {
                                    expert.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && mouth == 1) {
                                    expert.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && mouth == 1) {
                                    expert.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                }
                            }
                        }
                        if (grade.equals("")) {
                            expert.setSemester("");
                            expertAndCouncilor.setSemester("");
                        }

                    }
                    if (row.getCell(8) == null) {
                        expert.setClasses("");
                    } else {
                        expert.setClasses(cell7.getStringCellValue());
                    }
                    if (row.getCell(9) == null || cell8.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
                        expert.setStudentNumber(null);
                    } else {
                        String sNumber = String.valueOf(cell8.getNumericCellValue());

                        if (!sNumber.equals("")) {

                            if (sNumber.length() == 3){
                                String sNumberOne = sNumber.substring(0, 1);
                                int numberOne = Integer.valueOf(sNumberOne);
                                expert.setStudentNumber(numberOne);
                            }else if (sNumber.length() == 4){
                                String sNumberTwo = sNumber.substring(0, 2);
                                int numberTwo = Integer.valueOf(sNumberTwo);
                                expert.setStudentNumber(numberTwo);
                            }else if (sNumber.length() == 5){
                                String sNumberThree = sNumber.substring(0, 3);
                                int numberThree = Integer.valueOf(sNumberThree);
                                expert.setStudentNumber(numberThree);
                            }
                        } else if (sNumber.equals("")) {
                            expert.setStudentNumber(null);
                        }
                    }
                    if (row.getCell(10) == null) {
                        expert.setScore(null);
                        expertAndCouncilor.setExpertScore(null);
                        total.setExpertScore(null);
                    } else {
                        Double score = cell9.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        expert.setScore(scoreLast);
                        expertAndCouncilor.setExpertScore(scoreLast);
                        total.setExpertScore(scoreLast);
                    }
                    if (row.getCell(11) == null) {
                        expert.setNote("");
                    } else {
                        expert.setNote(cell10.getStringCellValue());
                    }
                    expertService.insertSelective(expert);
                    expertAndCouncilorService.insertSelective(expertAndCouncilor);
                    totalService.insertSelective(total);

                }
            } else if (suffix.equals(".xlsx")) {
                InputStream stream = new FileInputStream(fileName);
                wb2 = new XSSFWorkbook(stream);
                Sheet sheet1 = wb2.getSheetAt(0);

                for (int i = 1; i < 2; i++) {
                    Row row = sheet1.getRow(i);

                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);

                    if (!cell1.getStringCellValue().contains("序号")) {
                        model.addAttribute("error", "上传文件第一列非序号列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("部") && cell2.getStringCellValue().contains("系")) {

                        model.addAttribute("error", "上传文件第二列非系部列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("专业")) {

                        model.addAttribute("error", "上传文件第三列非专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("年级")) {

                        model.addAttribute("error", "上传文件第四列非年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("教师") && !cell5.getStringCellValue().contains("老师")) {

                        model.addAttribute("error", "上传文件第五列非授课教师[单位]列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("课程")) {

                        model.addAttribute("error", "上传文件第六列非课程名称列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell7.getStringCellValue().contains("日期")) {

                        model.addAttribute("error", "上传文件第七列非日期（节次）列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell8.getStringCellValue().contains("教室")) {

                        model.addAttribute("error", "上传文件第八列非教室列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell9.getStringCellValue().contains("班级")) {

                        model.addAttribute("error", "上传文件第九列非班级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell10.getStringCellValue().contains("人数")) {

                        model.addAttribute("error", "上传文件第十列非实到人数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell11.getStringCellValue().contains("分")) {

                        model.addAttribute("error", "上传文件第十一列非综合评分列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell12.getStringCellValue().contains("备注")) {

                        model.addAttribute("error", "上传文件第十二列非备注列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                for (int i = 2; i <= sheet1.getLastRowNum(); i++) {
                    Expert expert = new Expert();
                    ExpertAndCouncilor expertAndCouncilor = new ExpertAndCouncilor();
                    Total total = new Total();

                    Row row = sheet1.getRow(i);

                    if (null == row) {
                        break;
                    }
                    String grade = "";

                    Cell cell1 = row.getCell(1);
                    Cell cell2 = row.getCell(2);
                    Cell cell3 = row.getCell(3);
                    Cell cell4 = row.getCell(4);
                    Cell cell5 = row.getCell(5);
                    Cell cell6 = row.getCell(6);
                    Cell cell7 = row.getCell(8);
                    Cell cell8 = row.getCell(9);
                    Cell cell9 = row.getCell(10);
                    Cell cell10 = row.getCell(11);


                    if (row.getCell(6) == null && row.getCell(1) == null && row.getCell(2) == null && row.getCell(3) == null && row.getCell(4) == null && row.getCell(5) == null) {
                        continue;
                    }

                    if (row.getCell(1) == null) {
                        expert.setDepartment("");
                        expertAndCouncilor.setDepartment("");
                        total.setDepartment("");
                    } else {
                        if (cell1.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                            expert.setDepartment(cell1.getStringCellValue());
                            expertAndCouncilor.setDepartment(cell1.getStringCellValue());
                            total.setDepartment(cell1.getStringCellValue());
                        }else {
                            expert.setDepartment("");
                            expertAndCouncilor.setDepartment("");
                            total.setDepartment("");
                        }
                    }
                    if (row.getCell(2) == null) {
                        expert.setMajor("");
                        expertAndCouncilor.setMajor("");
                        total.setMajor("");
                    } else {
                        if (cell2.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                            expert.setMajor(cell2.getStringCellValue());
                            expertAndCouncilor.setMajor(cell2.getStringCellValue());
                            total.setMajor(cell2.getStringCellValue());
                        }else {
                            expert.setMajor("");
                            expertAndCouncilor.setMajor("");
                            total.setMajor("");
                        }
                    }
                    if (row.getCell(3) == null || cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        expert.setGrade("");
                        expertAndCouncilor.setGrade("");
                        total.setGrade("");
                    }
                    if (row.getCell(3) != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK) {

                        String errorGrade = cell3.getStringCellValue();
                        if (errorGrade.length() < 5) {
                            grade = "";
                            expert.setGrade("");
                            expertAndCouncilor.setGrade("");
                            total.setGrade("");
                        } else {
                            grade = cell3.getStringCellValue().substring(0, 4);
                            if (isNum(grade) == true){
                                expert.setGrade(grade);
                                expertAndCouncilor.setGrade(grade);
                                total.setGrade(grade);
                            }else {
                                expert.setGrade("");
                                expertAndCouncilor.setGrade("");
                                total.setGrade("");
                            }
                        }
                    }
                    if (row.getCell(4) == null) {
                        expert.setTeacher("");
                    } else {
                        expert.setTeacher(cell4.getStringCellValue());
                    }
                    if (row.getCell(5) == null) {
                        expert.setCourse("");
                        expertAndCouncilor.setCourse("");
                        total.setCourse("");
                    } else {
                        if (cell5.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                            expert.setCourse(cell5.getStringCellValue());
                            expertAndCouncilor.setCourse(cell5.getStringCellValue());
                            total.setCourse(cell5.getStringCellValue());
                        }else {
                            expert.setCourse("");
                            expertAndCouncilor.setCourse("");
                            total.setCourse("");
                        }
                    }
                    if (row.getCell(6) == null || cell6.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        expert.setSemester("");
                        expertAndCouncilor.setSemester("");
                    } else {
                        String sGrade = null;
                        int sGra = 0;
                        String sMouthOne = null;
                        String sMouthTwo = null;
                        switch (cell6.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sGrade = cell6.getStringCellValue().substring(0, 4);
                                if (isNum(sGrade) == true) {
                                    sGra = Integer.valueOf(sGrade).intValue();
                                }else {
                                    sGra = 0;
                                }
                                sMouthOne = cell6.getStringCellValue().substring(5, 7);
                                sMouthTwo = cell6.getStringCellValue().substring(5, 6);
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                                String data = String.valueOf(cell6.getDateCellValue().toString());
                                String date = dff.format(cell6.getDateCellValue());
                                sGrade = date.substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = date.substring(5, 7);
                                sMouthTwo = date.substring(5, 6);
                                break;
                        }

                        int mouth;
                        if (sMouthOne != null && sMouthTwo != null) {
                            if (isNum(sMouthOne) == true) {
                                mouth = Integer.valueOf(sMouthOne).intValue();
                            } else {
                                mouth = Integer.valueOf(sMouthTwo).intValue();
                            }
                            if (!grade.equals("")) {
                                String sGradeTwo = grade;
                                int iGrade = Integer.valueOf(sGradeTwo).intValue();

                                if ((sGra - iGrade - 1 == -1) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第一学年第二学期");
                                    expertAndCouncilor.setSemester("第一学年第二学期");
                                } else if ((sGra - iGrade == 1) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第二学年第二学期");
                                    expertAndCouncilor.setSemester("第二学年第二学期");
                                } else if ((sGra - iGrade == 2) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第三学年第二学期");
                                    expertAndCouncilor.setSemester("第三学年第二学期");
                                } else if ((sGra - iGrade == 3) && (9 <= mouth) && (mouth <= 12)) {
                                    expert.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                } else if ((sGra - iGrade == 4) && (2 <= mouth) && (mouth <= 8)) {
                                    expert.setSemester("第四学年第二学期");
                                    expertAndCouncilor.setSemester("第四学年第二学期");
                                } else if ((sGra - iGrade == 0) && mouth == 1) {
                                    expert.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && mouth == 1) {
                                    expert.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && mouth == 1) {
                                    expert.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && mouth == 1) {
                                    expert.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                }
                            }
                        }
                        if (grade.equals("")) {
                            expert.setSemester("");
                            expertAndCouncilor.setSemester("");
                        }

                    }
                    if (row.getCell(8) == null) {
                        expert.setClasses("");
                    } else {
                        expert.setClasses(cell7.getStringCellValue());
                    }
                    if (row.getCell(9) == null || cell8.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
                        expert.setStudentNumber(null);
                    } else {

                        String sNumber = String.valueOf(cell8.getNumericCellValue());
                        if (!sNumber.equals("")) {

                            if (sNumber.length() == 3){
                                String sNumberOne = sNumber.substring(0, 1);
                                int numberOne = Integer.valueOf(sNumberOne);
                                expert.setStudentNumber(numberOne);
                            }else if (sNumber.length() == 4){
                                String sNumberTwo = sNumber.substring(0, 2);
                                 int numberTwo = Integer.valueOf(sNumberTwo);
                                expert.setStudentNumber(numberTwo);
                            }else if (sNumber.length() == 5){
                                String sNumberThree = sNumber.substring(0, 3);
                                int numberThree = Integer.valueOf(sNumberThree);
                                expert.setStudentNumber(numberThree);
                            }

                        } else if (sNumber.equals("")) {
                            expert.setStudentNumber(null);
                        }
                    }
                    if (row.getCell(10) == null) {
                        expert.setScore(null);
                        expertAndCouncilor.setExpertScore(null);
                        total.setExpertScore(null);
                    } else {
                        Double score = cell9.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        expert.setScore(scoreLast);
                        expertAndCouncilor.setExpertScore(scoreLast);
                        total.setExpertScore(scoreLast);
                    }
                    if (row.getCell(11) == null) {
                        expert.setNote("");
                    } else {
                        expert.setNote(cell10.getStringCellValue());
                    }
                    expertService.insertSelective(expert);
                    expertAndCouncilorService.insertSelective(expertAndCouncilor);
                    totalService.insertSelective(total);

                }
            } else {
                model.addAttribute("error", "上传失败，请上传Excel类型的文件！");
                return "errorPage/format_error";
            }
            upload.setMd5(md);

            uploadService.insertSelective(upload);
            return "upload/upload_successful";
        } else {
            return "errorPage/upload_error";
        }

    }

    @RequestMapping(value = "/councilor/uploadCouncilor", method = RequestMethod.GET)
    public String uploadCouncilor(Model model, String md5) {

        model.addAttribute("md5", md5);


        return "page/upload_councilor";
    }

    @RequestMapping(value = "/councilor/uploadCouncilor", method = RequestMethod.POST)
    public String uploadCouncilors(Model model, HttpServletRequest request, String md5) throws IOException {

        Upload upload = new Upload();

        String md = request.getParameter("md5");


        List<Upload> uploads = uploadService.selectByMd5(md);


        if (uploads.isEmpty()) {

            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            String logoPathDir = "/resources/upload";

            String logoRealPathDir = request.getSession().getServletContext().getRealPath(logoPathDir);

            File logoSaveFile = new File(logoRealPathDir);
            if (!logoSaveFile.exists())
                logoSaveFile.mkdirs();
            /** 页面控件的文件流* */
            MultipartFile multipartFile = multipartRequest.getFile("files");
            /** 获取文件的后缀* */
            String suffix = multipartFile.getOriginalFilename().substring(
                    multipartFile.getOriginalFilename().lastIndexOf("."));

            /** 使用UUID生成文件名称* */
            String logImageName = UUID.randomUUID().toString() + suffix;// 构建文件名称
            /** 拼成完整的文件保存路径加文件* */
            String fileName = logoRealPathDir + File.separator + logImageName;
            File file = new File(fileName);
            try {
                multipartFile.transferTo(file);
            } catch (IllegalStateException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }

            HSSFWorkbook wb = null;
            XSSFWorkbook wb2 = null;


            if (suffix.equals(".xls")) {
                InputStream stream = new FileInputStream(fileName);
                wb = new HSSFWorkbook(stream);
                Sheet sheet1 = wb.getSheetAt(0);

                for (int i = 0; i < 1; i++) {
                    Row row = sheet1.getRow(i);
                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);
                    if (!cell1.getStringCellValue().contains("院") && !cell1.getStringCellValue().contains("系")) {
                        model.addAttribute("error", "上传文件第一列不是院系列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("专业")) {
                        model.addAttribute("error", "上传文件第二列不是专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("年级")) {
                        model.addAttribute("error", "上传文件第三列不是年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("教师") && !cell4.getStringCellValue().contains("老师")) {
                        model.addAttribute("error", "上传文件第四列不是授课教师列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("课程")) {
                        model.addAttribute("error", "上传文件第五列不是课程列，请参考模板修正");
                        return "/errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("时间")) {
                        model.addAttribute("error", "上传文件第六列不是时间（节数）列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell7.getStringCellValue().contains("地点")) {
                        model.addAttribute("error", "上传文件第七列不是地点列");
                        return "errorPage/format_error";
                    }
                    if (!cell8.getStringCellValue().contains("班级")) {
                        model.addAttribute("error", "上传文件第八列不是班级人数列");
                        return "errorPage/format_error";
                    }
                    if (!cell9.getStringCellValue().contains("分数")) {
                        model.addAttribute("error", "上传文件第九列不是分数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell10.getStringCellValue().contains("优点")) {
                        model.addAttribute("error", "上传文件第十列不是优点列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell11.getStringCellValue().contains("建议") && !cell11.getStringCellValue().contains("缺点") && !cell11.getStringCellValue().contains("学生问题")) {
                        model.addAttribute("error", "上传文件第十一列不是建议列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell12.getStringCellValue().contains("学生问题") && !cell12.getStringCellValue().contains("建议") && !cell12.getStringCellValue().contains("缺点")) {
                        model.addAttribute("error", "上传文件第十二列不是学生问题列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell13.getStringCellValue().contains("听课")) {
                        model.addAttribute("error", "上传文件第十三列不是听课教师列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell14.getStringCellValue().contains("听课次数")) {
                        model.addAttribute("error", "上传文件第十四列不是听课次数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
                    Councilor councilor = new Councilor();
                    ExpertAndCouncilor expertAndCouncilor = new ExpertAndCouncilor();
                    Total total = new Total();

                    Row row = sheet1.getRow(i);

                    if (null == row) {
                        break;
                    }
                    String grade = "";

                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);

                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);

                    if (row.getCell(0) == null && row.getCell(1) == null && row.getCell(2) == null && row.getCell(3) == null && row.getCell(4) == null && row.getCell(5) == null
                            ) {
                        continue;
                    }

                    if (row.getCell(0) == null) {
                        councilor.setDepartment("");
                    } else {
                        councilor.setDepartment(cell1.getStringCellValue());
                        expertAndCouncilor.setDepartment(cell1.getStringCellValue());
                        total.setDepartment(cell1.getStringCellValue());
                    }
                    if (row.getCell(1) == null) {
                        councilor.setMajor("");
                        expertAndCouncilor.setMajor("");
                        total.setMajor("");
                    } else {
                        councilor.setMajor(cell2.getStringCellValue());
                        expertAndCouncilor.setMajor(cell2.getStringCellValue());
                        total.setMajor(cell2.getStringCellValue());
                    }
                    if (row.getCell(2) == null || cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        councilor.setGrade("");
                        expertAndCouncilor.setGrade("");
                        total.setGrade("");
                    }
                    if (row.getCell(2) != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                        String errorGrade = cell3.getStringCellValue();
                        if (errorGrade.length() < 5) {
                            grade = "";
                            councilor.setGrade("");
                            expertAndCouncilor.setGrade("");
                            total.setGrade("");
                        } else {
                            grade = cell3.getStringCellValue().substring(0, 4);
                            councilor.setGrade(grade);
                            expertAndCouncilor.setGrade(grade);
                            total.setGrade(grade);
                        }
                    }
                    if (row.getCell(3) == null) {
                        councilor.setTeacher("");
                    } else {
                        councilor.setTeacher(cell4.getStringCellValue());
                    }
                    if (row.getCell(4) == null) {
                        councilor.setCourse("");
                        expertAndCouncilor.setCourse("");
                        total.setCourse("");
                    } else {
                        councilor.setCourse(cell5.getStringCellValue());
                        expertAndCouncilor.setCourse(cell5.getStringCellValue());
                        total.setCourse(cell5.getStringCellValue());
                    }
                    if (row.getCell(5) == null || cell6.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        councilor.setSemester("");
                        expertAndCouncilor.setSemester("");
                    } else {
                        String sGrade = null;
                        int sGra = 0;
                        String sMouthOne = null;
                        String sMouthTwo = null;
                        switch (cell6.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sGrade = cell6.getStringCellValue().substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = cell6.getStringCellValue().substring(5, 7);
                                sMouthTwo = cell6.getStringCellValue().substring(5, 6);
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                                String data = String.valueOf(cell6.getDateCellValue().toString());
                                String date = dff.format(cell6.getDateCellValue());
                                sGrade = date.substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = date.substring(5, 7);
                                sMouthTwo = date.substring(5, 6);
                                break;
                        }

                        int mouth;
                        if (sMouthOne != null && sMouthTwo != null) {
                            if (isNum(sMouthOne) == true) {
                                mouth = Integer.valueOf(sMouthOne).intValue();
                            } else {
                                mouth = Integer.valueOf(sMouthTwo).intValue();
                            }
                            if (!grade.equals("")) {
                                String sGradeTwo = grade;
                                int iGrade = Integer.valueOf(sGradeTwo).intValue();

                                if ((sGra - iGrade - 1 == -1) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第一学年第二学期");
                                    expertAndCouncilor.setSemester("第一学年第二学期");
                                } else if ((sGra - iGrade == 1) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第二学年第二学期");
                                    expertAndCouncilor.setSemester("第二学年第二学期");
                                } else if ((sGra - iGrade == 2) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第三学年第二学期");
                                    expertAndCouncilor.setSemester("第三学年第二学期");
                                } else if ((sGra - iGrade == 3) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                } else if ((sGra - iGrade == 4) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第四学年第二学期");
                                    expertAndCouncilor.setSemester("第四学年第二学期");
                                } else if ((sGra - iGrade == 0) && mouth == 1) {
                                    councilor.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && mouth == 1) {
                                    councilor.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && mouth == 1) {
                                    councilor.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && mouth == 1) {
                                    councilor.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                }
                            }
                        }
                        if (grade.equals("")) {
                            councilor.setSemester("");
                            expertAndCouncilor.setSemester("");
                        }

                    }
                    if (row.getCell(7) == null) {
                        councilor.setClasses("");
                    } else {
                        councilor.setClasses(cell8.getStringCellValue());
                    }
                    if (row.getCell(8) == null) {
                        councilor.setScore(null);
                        expertAndCouncilor.setCouncilorScore(null);
                        total.setCouncilorScore(null);
                    } else {
                        Double score = cell9.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        councilor.setScore(scoreLast);
                        expertAndCouncilor.setCouncilorScore(scoreLast);
                        total.setCouncilorScore(scoreLast);
                    }
                    if (row.getCell(9) == null) {
                        councilor.setEvaluate("");
                    } else {
                        councilor.setEvaluate(cell10.getStringCellValue());
                    }
                    if (row.getCell(10) == null) {
                        councilor.setSuggest("");
                    } else {
                        councilor.setSuggest(cell11.getStringCellValue());
                    }
                    if (row.getCell(11) == null) {
                        councilor.setProblem("");
                    } else {
                        councilor.setProblem(cell12.getStringCellValue());
                    }
                    if (row.getCell(12) == null) {
                        councilor.setCouncilorName("");
                    } else {
                        councilor.setCouncilorName(cell13.getStringCellValue());
                    }
                    if (row.getCell(13) == null) {
                        councilor.setTimes("");
                    } else {
                        councilor.setTimes(cell14.getStringCellValue());
                    }
                    councilorService.insertSelective(councilor);
                    expertAndCouncilorService.insertSelective(expertAndCouncilor);
                    totalService.insertSelective(total);
                }
            } else if (suffix.equals(".xlsx")) {
                InputStream stream = new FileInputStream(fileName);
                wb2 = new XSSFWorkbook(stream);
                Sheet sheet1 = wb2.getSheetAt(0);

                for (int i = 0; i < 1; i++) {
                    Row row = sheet1.getRow(i);
                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);
                    if (!cell1.getStringCellValue().contains("院") && !cell1.getStringCellValue().contains("系")) {
                        model.addAttribute("error", "上传文件第一列不是院系列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell2.getStringCellValue().contains("专业")) {
                        model.addAttribute("error", "上传文件第二列不是专业列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell3.getStringCellValue().contains("年级")) {
                        model.addAttribute("error", "上传文件第三列不是年级列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell4.getStringCellValue().contains("教师") && !cell4.getStringCellValue().contains("老师")) {
                        model.addAttribute("error", "上传文件第四列不是授课教师列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell5.getStringCellValue().contains("课程")) {
                        model.addAttribute("error", "上传文件第五列不是课程列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell6.getStringCellValue().contains("时间")) {
                        model.addAttribute("error", "上传文件第六列不是时间（节数）列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell7.getStringCellValue().contains("地点")) {
                        model.addAttribute("error", "上传文件第七列不是地点列");
                        return "errorPage/format_error";
                    }
                    if (!cell8.getStringCellValue().contains("班级")) {
                        model.addAttribute("error", "上传文件第八列不是班级人数列");
                        return "errorPage/format_error";
                    }
                    if (!cell9.getStringCellValue().contains("分数")) {
                        model.addAttribute("error", "上传文件第九列不是分数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell10.getStringCellValue().contains("优点")) {
                        model.addAttribute("error", "上传文件第十列不是优点列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell11.getStringCellValue().contains("建议") && !cell11.getStringCellValue().contains("缺点") && !cell11.getStringCellValue().contains("学生问题")) {
                        model.addAttribute("error", "上传文件第十一列不是建议列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell12.getStringCellValue().contains("学生问题") && !cell12.getStringCellValue().contains("建议") && !cell12.getStringCellValue().contains("缺点")) {
                        model.addAttribute("error", "上传文件第十二列不是学生问题列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell13.getStringCellValue().contains("听课")) {
                        model.addAttribute("error", "上传文件第十三列不是听课教师列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                    if (!cell14.getStringCellValue().contains("听课次数")) {
                        model.addAttribute("error", "上传文件第十四列不是听课次数列，请参考模板修正");
                        return "errorPage/format_error";
                    }
                }

                for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
                    Councilor councilor = new Councilor();
                    ExpertAndCouncilor expertAndCouncilor = new ExpertAndCouncilor();
                    Total total = new Total();

                    Row row = sheet1.getRow(i);

                    if (null == row) {
                        break;
                    }

                    String grade = "";

                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);

                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);
                    Cell cell10 = row.getCell(9);
                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);

                    if (row.getCell(0) == null && row.getCell(1) == null && row.getCell(2) == null && row.getCell(3) == null && row.getCell(4) == null && row.getCell(5) == null
                            ) {
                        continue;
                    }

                    if (row.getCell(0) == null) {
                        councilor.setDepartment("");
                    } else {
                        councilor.setDepartment(cell1.getStringCellValue());
                        expertAndCouncilor.setDepartment(cell1.getStringCellValue());
                        total.setDepartment(cell1.getStringCellValue());
                    }
                    if (row.getCell(1) == null) {
                        councilor.setMajor("");
                        expertAndCouncilor.setMajor("");
                        total.setMajor("");
                    } else {
                        councilor.setMajor(cell2.getStringCellValue());
                        expertAndCouncilor.setMajor(cell2.getStringCellValue());
                        total.setMajor(cell2.getStringCellValue());
                    }
                    if (row.getCell(2) == null || cell3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        councilor.setGrade("");
                        expertAndCouncilor.setGrade("");
                        total.setGrade("");
                    }
                    if (row.getCell(2) != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK) {

                        String errorGrade = cell3.getStringCellValue();
                        if (errorGrade.length() < 5) {
                            grade = "";
                            councilor.setGrade("");
                            expertAndCouncilor.setGrade("");
                            total.setGrade("");
                        } else {
                            grade = cell3.getStringCellValue().substring(0, 4);
                            councilor.setGrade(grade);
                            expertAndCouncilor.setGrade(grade);
                            total.setGrade(grade);
                        }
                    }
                    if (row.getCell(3) == null) {
                        councilor.setTeacher("");
                    } else {
                        councilor.setTeacher(cell4.getStringCellValue());
                    }
                    if (row.getCell(4) == null) {
                        councilor.setCourse("");
                        expertAndCouncilor.setCourse("");
                        total.setCourse("");
                    } else {
                        councilor.setCourse(cell5.getStringCellValue());
                        expertAndCouncilor.setCourse(cell5.getStringCellValue());
                        total.setCourse(cell5.getStringCellValue());
                    }
                    if (row.getCell(5) == null || cell6.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        councilor.setSemester("");
                        expertAndCouncilor.setSemester("");
                    } else {
                        String sGrade = null;
                        int sGra = 0;
                        String sMouthOne = null;
                        String sMouthTwo = null;
                        switch (cell6.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                sGrade = cell6.getStringCellValue().substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = cell6.getStringCellValue().substring(5, 7);
                                sMouthTwo = cell6.getStringCellValue().substring(5, 6);
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                                String data = String.valueOf(cell6.getDateCellValue().toString());
                                String date = dff.format(cell6.getDateCellValue());
                                sGrade = date.substring(0, 4);
                                sGra = Integer.valueOf(sGrade).intValue();
                                sMouthOne = date.substring(5, 7);
                                sMouthTwo = date.substring(5, 6);
                                break;
                        }

                        int mouth;

                        if (sMouthOne != null && sMouthTwo != null) {
                            if (isNum(sMouthOne) == true) {
                                mouth = Integer.valueOf(sMouthOne).intValue();
                            } else {
                                mouth = Integer.valueOf(sMouthTwo).intValue();
                            }
                            if (!grade.equals("")) {
                                String sGradeTwo = grade;
                                int iGrade = Integer.valueOf(sGradeTwo).intValue();

                                if ((sGra - iGrade - 1 == -1) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第一学年第二学期");
                                    expertAndCouncilor.setSemester("第一学年第二学期");
                                } else if ((sGra - iGrade == 1) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第二学年第二学期");
                                    expertAndCouncilor.setSemester("第二学年第二学期");
                                } else if ((sGra - iGrade == 2) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第三学年第二学期");
                                    expertAndCouncilor.setSemester("第三学年第二学期");
                                } else if ((sGra - iGrade == 3) && (9 <= mouth) && (mouth <= 12)) {
                                    councilor.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                } else if ((sGra - iGrade == 4) && (2 <= mouth) && (mouth <= 8)) {
                                    councilor.setSemester("第四学年第二学期");
                                    expertAndCouncilor.setSemester("第四学年第二学期");
                                } else if ((sGra - iGrade == 0) && mouth == 1) {
                                    councilor.setSemester("第一学年第一学期");
                                    expertAndCouncilor.setSemester("第一学年第一学期");
                                } else if ((sGra - iGrade == 1) && mouth == 1) {
                                    councilor.setSemester("第二学年第一学期");
                                    expertAndCouncilor.setSemester("第二学年第一学期");
                                } else if ((sGra - iGrade == 2) && mouth == 1) {
                                    councilor.setSemester("第三学年第一学期");
                                    expertAndCouncilor.setSemester("第三学年第一学期");
                                } else if ((sGra - iGrade == 3) && mouth == 1) {
                                    councilor.setSemester("第四学年第一学期");
                                    expertAndCouncilor.setSemester("第四学年第一学期");
                                }
                            }
                        }
                        if (grade.equals("")) {
                            councilor.setSemester("");
                            expertAndCouncilor.setSemester("");
                        }

                    }
                    if (row.getCell(7) == null) {
                        councilor.setClasses("");
                    } else {
                        councilor.setClasses(cell8.getStringCellValue());
                    }
                    if (row.getCell(8) == null) {
                        councilor.setScore(null);
                        expertAndCouncilor.setCouncilorScore(null);
                        total.setCouncilorScore(null);
                    } else {
                        Double score = cell9.getNumericCellValue();
                        String score1 = String.valueOf(score);
                        Float scoreLast = Float.valueOf(score1);
                        councilor.setScore(scoreLast);
                        expertAndCouncilor.setCouncilorScore(scoreLast);
                        total.setCouncilorScore(scoreLast);
                    }
                    if (row.getCell(9) == null) {
                        councilor.setEvaluate("");
                    } else {
                        councilor.setEvaluate(cell10.getStringCellValue());
                    }
                    if (row.getCell(10) == null) {
                        councilor.setSuggest("");
                    } else {
                        councilor.setSuggest(cell11.getStringCellValue());
                    }
                    if (row.getCell(11) == null) {
                        councilor.setProblem("");
                    } else {
                        councilor.setProblem(cell12.getStringCellValue());
                    }
                    if (row.getCell(12) == null) {
                        councilor.setCouncilorName("");
                    } else {
                        councilor.setCouncilorName(cell13.getStringCellValue());
                    }
                    if (row.getCell(13) == null) {
                        councilor.setTimes("");
                    } else {
                        councilor.setTimes(cell14.getStringCellValue());
                    }
                    councilorService.insertSelective(councilor);
                    expertAndCouncilorService.insertSelective(expertAndCouncilor);
                    totalService.insertSelective(total);
                }
            } else {
                model.addAttribute("error", "上传失败，请上传Excel类型的文件！");
                return "errorPage/format_error";
            }
            upload.setMd5(md);

            uploadService.insertSelective(upload);
            return "upload/upload_successful";
        } else {
            return "errorPage/upload_error";
        }
    }

    public static boolean isNum(String str) {

        return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
    }

}
